﻿using QuanLyThuVien.DTO;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using QuanLyThuVien.Supporter;
using System.Data.SqlClient;

namespace QuanLyThuVien.DAL
{
    //----------------------------------------------------------------------
    //Desc: class kết nối với bảng TACGIA
    //----------------------------------------------------------------------
    public class DALTacGia
    {
        //----------------------------------------------------------------------
        //Desc: constructor mặc định
        //----------------------------------------------------------------------
        public DALTacGia()
        { }

        //----------------------------------------------------------------------
        //Desc: chuyển dòng dữ liệu thành TACGIA
        //----------------------------------------------------------------------
        public DTOTacGia ConvertToTacGia(DataRow row)
        {
            if (DatabaseManager.DbConnection == null)
                return null;
            DTOTacGia tacgia = new DTOTacGia();
            tacgia.MaTG = row["MaTacGia"].ToString();
            tacgia.TenTG = row["TenTacGia"].ToString();

            return tacgia;
        }

        //----------------------------------------------------------------------
        //Desc: lấy danh sách tác giả
        //----------------------------------------------------------------------
        public List<DTOTacGia> LayDSTacGia()
        {
            if (DatabaseManager.DbConnection == null)
                return null;
            string spName = "spTacGia_LayDanhSachTG";
            DataTable data = DatabaseManager.DbConnection.ExecuteStoredProcedure(spName);
            if (data == null)
                return null;
            else
            {
                List<DTOTacGia> list = new List<DTOTacGia>();
                for (int i = 0; i < data.Rows.Count; i++)
                    list.Add(ConvertToTacGia(data.Rows[i]));
                return list;
            }
        }

        //----------------------------------------------------------------------
        //Desc: lấy danh sách tác giả, trả về datatable
        //----------------------------------------------------------------------
        public DataTable LayDanhSachTacGia()
        {
            if (DatabaseManager.DbConnection == null)
                return null;
            string spName = "spTacGia_LayDanhSachTG";
            return DatabaseManager.DbConnection.ExecuteStoredProcedure(spName);
        }

        //----------------------------------------------------------------------
        //Desc: lấy thông tin tác giả
        //----------------------------------------------------------------------
        public DTOTacGia LayThongTinTacGia(string matg)
        {
            if (DatabaseManager.DbConnection == null)
                return null;
            string spName = "spTacGia_LayThongTinTG";
            SqlParameter sqlprMaTacGia = new SqlParameter("@MaTacGia", SqlDbType.VarChar, 15) { Value = matg };
            DataTable data = DatabaseManager.DbConnection.ExecuteStoredProcedure(spName, sqlprMaTacGia);
            if (data == null || data.Rows.Count == 0)
                return null;
            return ConvertToTacGia(data.Rows[0]);
        }

        //----------------------------------------------------------------------
        //Desc: lấy tên tác giả
        //----------------------------------------------------------------------
        public string LayTenTG(string matg)
        {
            if (DatabaseManager.DbConnection == null)
                return null;
            string spName = "spTacGia_LayTenTG";
            SqlParameter sqlprMaTacGia = new SqlParameter("@MaTacGia", SqlDbType.VarChar, 15) { Value = matg };
            DataTable data = DatabaseManager.DbConnection.ExecuteStoredProcedure(spName, sqlprMaTacGia);
            if (data == null)
                return null;
            return data.Rows[0][0].ToString();
        }

        //----------------------------------------------------------------------
        //Desc: đếm tác giả
        //----------------------------------------------------------------------
        public int DemTacGia()
        {
            if (DatabaseManager.DbConnection == null)
                return -1;
            string spName = "spTacGia_DemTG";
            object obj = DatabaseManager.DbConnection.ExecuteStoredProcedureScalar(spName);
            if (obj == null)
                return -1;

            int dem;
            try
            {
                dem = (int)obj;
            }
            catch { return -1; }
            return dem;
        }

        //----------------------------------------------------------------------
        //Desc: đếm mã tác giả
        //----------------------------------------------------------------------
        public int DemMaTacGia(string matacgia)
        {
            if (DatabaseManager.DbConnection == null)
                return -1;
            string spName = "spTacGia_DemMaTG";
            SqlParameter sqlprMaTG = new SqlParameter("@MaTacGia", SqlDbType.VarChar, 15) { Value = matacgia };
            object obj = DatabaseManager.DbConnection.ExecuteStoredProcedureScalar(spName, sqlprMaTG);
            if (obj == null)
                return -1;

            int dem;
            try
            {
                dem = (int)obj;
            }
            catch { return -1; }
            return dem;
        }

        //----------------------------------------------------------------------
        //Desc: đếm số đầu sách của tác giả
        //----------------------------------------------------------------------
        public int DemSoDauSachCuaTG(string matacgia)
        {
            if (DatabaseManager.DbConnection == null)
                return -1;
            string spName = "spTacGia_DemSoDauSach";
            SqlParameter sqlprMaTG = new SqlParameter("@MaTacGia", SqlDbType.VarChar, 15) { Value = matacgia };
            object obj = DatabaseManager.DbConnection.ExecuteStoredProcedureScalar(spName, sqlprMaTG);
            if (obj == null)
                return -1;

            int dem;
            try
            {
                dem = (int)obj;
            }
            catch { return -1; }
            return dem;
        }

        //----------------------------------------------------------------------
        //Desc: cập nhật tác giả
        //----------------------------------------------------------------------
        public bool CapNhat(DTOTacGia tacgia)
        {
            if (DatabaseManager.DbConnection == null)
                return false;
            string spName = "spTacGia_CapNhatTG";
            SqlParameter sqlprThaoTac = new SqlParameter("@ThaoTac", SqlDbType.Int) { Value = 1 };
            SqlParameter sqlprMaTG = new SqlParameter("@MaTacGia", SqlDbType.VarChar, 15) { Value = tacgia.MaTG };
            SqlParameter sqlprTenTG = new SqlParameter("@TenTacGia", SqlDbType.NVarChar, 70) { Value = tacgia.TenTG };
            return DatabaseManager.DbConnection.ExecuteStoredProcedureNonQuery(spName, sqlprThaoTac, sqlprMaTG,
                sqlprTenTG);
        }

        //----------------------------------------------------------------------
        //Desc: xóa tác giả
        //----------------------------------------------------------------------
        public bool Xoa(DTOTacGia tacgia)
        {
            if (DatabaseManager.DbConnection == null)
                return false;
            string spName = "spTacGia_CapNhatTG";
            SqlParameter sqlprThaoTac = new SqlParameter("@ThaoTac", SqlDbType.Int) { Value = 0 };
            SqlParameter sqlprMaTG = new SqlParameter("@MaTacGia", SqlDbType.VarChar, 15) { Value = tacgia.MaTG };
            SqlParameter sqlprTenTG = new SqlParameter("@TenTacGia", SqlDbType.NVarChar, 70) { Value = tacgia.TenTG };
            return DatabaseManager.DbConnection.ExecuteStoredProcedureNonQuery(spName, sqlprThaoTac, sqlprMaTG,
                sqlprTenTG);
        }
    }
}
